﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using LCUV;
using System.Web.Configuration;
using Oracle.DataAccess.Client;

namespace sWarranty.WEB
{
    public partial class ShopREGxls : System.Web.UI.Page
    {
        public string _currentUserEmpNo = "";
        public string _currentUserAuthority = "";
        protected void Page_Load(object sender, EventArgs e)
        {

            Session["UrltoFw"] = "ShopREGxls.aspx";
            _currentUserEmpNo = GetUserLoginEmpNo();

            if (!IsPostBack)
            {
                Session["filePath"] = "";
                Session["importTable"] = "";//table after import data
                Session["fileName"] = Guid.NewGuid().ToString();
            }

        }
        protected string GetUserLoginEmpNo()
        {
            string t = "";
            try
            {
                if (Session["UserLoginInfo"] != null)
                {
                    Entities db = new Entities();
                    var u = (TB_USER[])Session["UserLoginInfo"];
                    int _UID = Convert.ToInt32(u.Single().ID);
                    var au = from ft in db.TB_USER_LEVEL
                             where ft.USER_ID == _UID
                             select ft;
                    int _le = Convert.ToInt32(au.Single().LEVEL_ID);
                    var le = from xx in db.TB_LEVEL
                             where xx.ID == _le
                             select xx;
                    _currentUserAuthority = le.Single().REMARK;
                    return u.Single().EMPNO;
                }
            }
            catch (Exception)
            {
                t = "";
            }
            return t;
        }

        protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
        {

            string fileName = Session["fileName"].ToString();
            string _filePath = Request.PhysicalApplicationPath + fileName + ".xls";// +AsyncFileUpload1.PostedFile.FileName;
            if (File.Exists(_filePath))
            {
                File.Delete(_filePath);
            }
            else
            {
                AsyncFileUpload1.SaveAs(_filePath);
                //_filePath = EncDec.Encrypt(_filePath, "");
                Session["filePath"] = _filePath;
                txtFileName.Text = fileName;
                //ScriptManager.RegisterClientScriptBlock(AsyncFileUpload1, AsyncFileUpload1.GetType(), Guid.NewGuid().ToString(), "ShowExcelData()", true);
                //readExcel(false);
            }
        }

        //Validate xls file
        protected string readExcel(bool SaveToDB)
        {
            string path = Session["filePath"].ToString();
            string fileName = Session["fileName"].ToString();
            string ReturnValue = "";
            txtBillToUnSave.Text = "";
            try
            {

                if (!path.EndsWith(".xls"))
                {
                    ReturnValue = "System just support Excel version 2003 (*.xls) only";
                    return ReturnValue;
                }
                if (string.IsNullOrEmpty(path))
                {
                    ReturnValue = "There are something wrong with file path. please reload page and import again";
                    return ReturnValue;
                }
                if (!File.Exists(path))
                {
                    ReturnValue = "File not found. Please select new file to upload";
                    return ReturnValue;
                }
                DataTable dtExcel = new DataTable();
                string err = "";
                dtExcel.TableName = "MyExcelData";
                string SourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
                OleDbConnection con = new OleDbConnection(SourceConstr);
                string query = "Select * from [Sheet1$]";
                OleDbDataAdapter data = new OleDbDataAdapter(query, con);
                data.Fill(dtExcel);
                data.Dispose();
                string st = "";
                int total = 0;
                string BILLTO_CODE = "";
                string BILLTO_NAME = "";
                string SHOP_CODE = "";
                string SHOP_NAME = "";
                string SHOP_ADDRESS = "";
                string CELL_NO1 = "";
                string DIST_CODE = "";
                string ISDN_NO = "";
                string CUSTOMER_CODE = "";
                string CUSTOMER_NAME = "";
                string REG_ID = "";
                string CELL_NO2 = "";
                string OWNER_NAME = "";
                string OWNER_EMAIL = "";
                string CHANNEL = "";
                string SHOP_TYPE = "";
                string CREATE_USER = "";
                string DIST_NAME = "";

                Entities db = new Entities();
                //TB_CM_SHOP_INF sh[] = new TB_CM_SHOP_INF();
                TB_CM_SHOP_INF[] sh = new TB_CM_SHOP_INF[1000];

                foreach (DataRow item in dtExcel.Rows)
                {
                    if (!string.IsNullOrEmpty(item["BILLTO_CODE"].ToString()))
                    {
                        BILLTO_CODE = item["BILLTO_CODE"].ToString();
                        var bill = from t in db.TB_CM_BILLTO_INF
                                   where t.BILLTO_CODE == BILLTO_CODE
                                   select t;
                        if (bill.Count() == 1)
                        {
                            err = "";
                            sh[total] = new TB_CM_SHOP_INF();
                            sh[total].BILLTO_CODE = BILLTO_CODE;
                            sh[total].SHOP_CODE = DBAction.GetNewShopCode(total);
                            SHOP_CODE = sh[total].SHOP_CODE;
                            BILLTO_NAME = bill.Single().FULL_NAME;

                            CUSTOMER_CODE = bill.Single().CUSTOMER_CODE;
                            sh[total].CUSTOMER_CODE = CUSTOMER_CODE;
                            CUSTOMER_NAME = bill.Single().ADDRESS;
                            if (!string.IsNullOrEmpty(item["SHOP_NAME"].ToString()))
                            {
                                SHOP_NAME = item["SHOP_NAME"].ToString();
                                if (DBAction.ShopNameExistOrNot(SHOP_NAME) == true)
                                    err += "SHOP_NAME exists; ";
                                else
                                    sh[total].SHOP_NAME = SHOP_NAME;
                            }
                            else
                            {
                                err += "SHOP_NAME empty; ";
                            }
                            if (!string.IsNullOrEmpty(item["ADDRESS"].ToString()))
                            {
                                SHOP_ADDRESS = item["ADDRESS"].ToString();
                                sh[total].ADDRESS = SHOP_ADDRESS;
                            }
                            else
                            {
                                err += "ADDRESS empty; ";
                            }
                            ISDN_NO = item["ISDN_NO"].ToString();
                            sh[total].ISDN_NO = ISDN_NO;
                            if (!string.IsNullOrEmpty(item["DIST_CODE"].ToString()))
                            {
                                DIST_CODE = item["DIST_CODE"].ToString();
                                sh[total].DIST_CODE = DIST_CODE;
                                sh[total].PROVINCE_CODE = DBAction.GetProvinceID(Convert.ToInt32(DIST_CODE)).ToString();
                            }
                            else
                            {
                                err += "DIST_CODE empty; ";
                            }
                            DIST_NAME = item["DIST_NAME"].ToString();
                            REG_ID = item["REGION_CODE"].ToString();
                            if ((REG_ID != "01") && (REG_ID != "02") && (REG_ID != "03"))
                            {
                                err += ("Region_code must be '01' or '02' or '03'.");

                            }
                            sh[total].REG_ID = REG_ID;
                            CELL_NO2 = item["CELL_NO2"].ToString().Replace(" ", "").Replace(".", "").Replace(",", "").Replace("-", "");
                            if (!string.IsNullOrEmpty(CELL_NO2))
                            {
                                if (CELL_NO2.Length < 9 || CELL_NO2.Length > 12)
                                {
                                    err += ("Cell#2 must greater than 9 and lower than 12");
                                }
                                else
                                {
                                    if (CELL_NO2.StartsWith("09") == true || CELL_NO2.StartsWith("01") == true || CELL_NO2.StartsWith("06") == true)
                                    {
                                        if (DBAction.ShopCellExistOrNot(CELL_NO2) == true)
                                            err += "SHOP_CELL#2 exists; ";
                                        else
                                            sh[total].CELL_NO_2 = "84" + CELL_NO2.Remove(0, 1);
                                    }
                                    else
                                    {
                                        err += ("Cell#2 must start with '09' or '01' or '06'");
                                    }
                                }

                            }

                            OWNER_NAME = item["OWNER_NAME"].ToString();
                            sh[total].OWNER_NAME = OWNER_NAME;
                            OWNER_EMAIL = item["OWNER_EMAIL"].ToString();
                            sh[total].OWNER_EMAIL = OWNER_EMAIL;
                            CELL_NO1 = item["CELL_NO1"].ToString().Replace(" ", "").Replace(".", "").Replace(",", "").Replace("-", "");
                            if (string.IsNullOrEmpty(CELL_NO1))
                            {
                                err += ("User must input Cell#1");
                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(CELL_NO1))
                                {
                                    if (CELL_NO1.Length < 9 || CELL_NO1.Length > 12)
                                    {
                                        err += ("Cell#1 must greater than 9 and lower than 12");
                                    }
                                    else
                                    {
                                        if (CELL_NO1.StartsWith("09") == true || CELL_NO1.StartsWith("01") == true || CELL_NO1.StartsWith("06") == true)
                                        {
                                            if (DBAction.ShopCellExistOrNot(CELL_NO1) == true)
                                                err += "SHOP_CELL#1 exists; ";
                                            else
                                                sh[total].CELL_NO_1 = "84" + CELL_NO1.Remove(0, 1);
                                        }
                                        else
                                        {
                                            err += ("Cell#1 must start with '09' or '01' or '06'");
                                        }
                                    }

                                }
                            }

                            CREATE_USER = _currentUserEmpNo;
                            sh[total].CREATE_USER = CREATE_USER;
                            CHANNEL = item["CHANNEL"].ToString();
                            if (CHANNEL.ToUpper() == "DIRECT" || CHANNEL.ToUpper() == "IN-DIRECT")
                            {
                                if (CHANNEL.ToUpper() == "DIRECT")
                                    sh[total].CHANNEL = "Y";
                                else
                                    sh[total].CHANNEL = "N";
                            }
                            else
                            {
                                err += "CHANNEL is not 'Direct' or 'In-Direct'; ";
                            }

                            SHOP_TYPE = item["SHOP_TYPE"].ToString();
                            if (SHOP_TYPE.ToUpper() == "SUB-D" || SHOP_TYPE.ToUpper() == "MC" || SHOP_TYPE.ToUpper() == "WS" || SHOP_TYPE.ToUpper() == "DSP")
                            {
                                sh[total].SHOP_TYPE = SHOP_TYPE;
                            }
                            else
                            {
                                err += "CHANNEL is not 'SUB-D' or 'MC' or 'WS' or 'DSP'; ";
                            }

                            sh[total].USE_FLAG = "Y";
                            total++;

                            if (!string.IsNullOrEmpty(err))
                            {
                                st += "<tr style='background:gray;'>";
                                st += "<td>" + total + "</td>";
                                st += "  <td><input id='" + SHOP_CODE + "' type='checkbox' onclick=\"ttt(this)\" disabled='disabled'/></td>";
                            }
                            else
                            {
                                st += "<tr>";
                                st += "<td>" + total + "</td>";
                                st += "  <td><input id='" + SHOP_CODE + "' type='checkbox' onclick=\"ttt(this)\" checked='checked'/></td>";
                            }
                            st += "  <td>" + BILLTO_CODE + "</td>";
                            st += "  <td>" + BILLTO_NAME + "</td>";
                            st += "  <td>" + SHOP_NAME + "</td>";
                            st += "  <td>" + SHOP_ADDRESS + "</td>";
                            st += "  <td>" + CELL_NO1 + "</td>";
                            st += "  <td>" + DIST_CODE + "</td>";
                            st += "  <td>" + DIST_NAME + "</td>";
                            st += "  <td>" + CUSTOMER_CODE + "</td>";
                            st += "  <td>" + CUSTOMER_NAME + "</td>";
                            st += "  <td>" + REG_ID + "</td>";
                            st += "  <td>" + CELL_NO2 + "</td>";
                            st += "  <td>" + ISDN_NO + "</td>";
                            st += "  <td>" + OWNER_NAME + "</td>";
                            st += "  <td>" + OWNER_EMAIL + "</td>";
                            st += "  <td>" + CHANNEL + "</td>";
                            st += "  <td>" + SHOP_TYPE  + "</td>";
                            st += "  <td>" + err + "</td>";
                            st += "</tr>";
                            if (!string.IsNullOrEmpty(err))
                                txtBillToUnSave.Text += SHOP_CODE + "||";
                        }

                        if (bill.Count() > 1)
                            return "There are duplicate BILLTO CODE: " + item["BILLTO_CODE"].ToString() + ". Please check with your administrator";
                        if (bill.Count() == 0)
                            break;
                    }
                }

                ReturnValue = st;
                Session["importTable"] = sh.ToArray();

            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error: " + ee.InnerException.Message;
                else
                    return "Error: " + ee.Message;
            }
            return ReturnValue;
        }

        //btn Save
        protected void btnReadFile_Click(object sender, EventArgs e)
        {
            try
            {
                var sh = (TB_CM_SHOP_INF[])Session["importTable"];
                Entities db = new Entities();
                int i = 0;
                string query = "";
                string err = "";
                foreach (var item in sh)
                {
                    try
                    {
                        if (item != null)
                        {
                            if (!txtBillToUnSave.Text.Contains(item.SHOP_CODE) && !string.IsNullOrEmpty(item.SHOP_CODE))
                            {
                                //TB_CM_SHOP_INF s = new TB_CM_SHOP_INF();
                                query = "Insert into TB_CM_SHOP_INF (SHOP_CODE,SHOP_NAME,ADDRESS,REG_ID,BILLTO_CODE,CELL_NO#1,CELL_NO#2,CUSTOMER_CODE,DIST_CODE,PROVINCE_CODE,ISDN_NO,OWNER_EMAIL,OWNER_NAME,USE_FLAG,CHANNEL,CREATE_USER,CREATE_DATE) values (";
                                query += "'" + item.SHOP_CODE + "',";
                                query += "'" + item.SHOP_NAME + "',";
                                query += "'" + item.ADDRESS + "',";
                                query += "'" + item.REG_ID + "',";
                                query += "'" + item.BILLTO_CODE + "',";
                                query += "'" + item.CELL_NO_1 + "',";
                                query += "'" + item.CELL_NO_2 + "',";
                                query += "'" + item.CUSTOMER_CODE + "',";
                                query += "'" + item.DIST_CODE + "',";
                                query += "'" + DBAction.GetProvinceID(Convert.ToInt32(item.DIST_CODE)).ToString() + "',";
                                query += "'" + item.ISDN_NO + "',";
                                query += "'" + item.OWNER_EMAIL + "',";
                                query += "'" + item.OWNER_NAME + "',";
                                query += "'" + "Y" + "',";
                                query += "'" + item.CHANNEL + "',";
                                query += "'" + item.CREATE_USER + "',";
                                query += "SYSDATE";
                                query += ")";

                                OracleCommand cmd = DBAction.con.CreateCommand();
                                OracleParameter param = new OracleParameter();
                                cmd.CommandText = query;
                                cmd.CommandType = CommandType.Text;
                                var x = cmd.ExecuteScalar();


                                if (x != null)
                                    ShowError(x.ToString());
                                else
                                {
                                    //save bill mac dinh(only 1) vao shop_Bill va dat use = Y
                                    TB_CM_SHOP_BILLTO b = new TB_CM_SHOP_BILLTO();
                                    b.BILLTO_CODE = item.BILLTO_CODE;
                                    b.SHOP_CODE = item.SHOP_CODE;
                                    b.CREATE_DATE = DateTime.Now;
                                    b.CREATE_USER = _currentUserEmpNo;
                                    b.USE_FLAG = "Y";
                                    db.TB_CM_SHOP_BILLTO.AddObject(b);

                                    //save fone vao shop_cel va dat use = Y
                                    TB_CM_SHOP_CELL c;
                                    if (!string.IsNullOrEmpty(item.CELL_NO_1))
                                    {
                                        c = new TB_CM_SHOP_CELL();
                                        c.SHOP_ID = item.SHOP_CODE;
                                        c.SHOP_CELL = item.CELL_NO_1;
                                        c.CREATE_DATE = DateTime.Now;
                                        c.CREATE_USER = _currentUserEmpNo;
                                        c.USE_FLAG = "Y";
                                        db.TB_CM_SHOP_CELL.AddObject(c);
                                    }

                                    if (!string.IsNullOrEmpty(item.CELL_NO_2))
                                    {
                                        c = new TB_CM_SHOP_CELL();
                                        c.SHOP_ID = item.SHOP_CODE;
                                        c.SHOP_CELL = item.CELL_NO_2;
                                        c.CREATE_DATE = DateTime.Now;
                                        c.CREATE_USER = _currentUserEmpNo;
                                        c.USE_FLAG = "Y";
                                        db.TB_CM_SHOP_CELL.AddObject(c);
                                    }
                                }
                                db.SaveChanges();
                                i++;
                            }
                        }
                    }
                    catch (Exception ee)
                    {
                        //remove shop_inf vua insert vao
                        query = "delete TB_CM_SHOP_INF where shop_code = '" + item.SHOP_CODE + "'";
                        DBAction.RunStore(query);
                        ShowError(ee);
                    }
                }
                ShowSuccess(i + " item imported.");


                //xoa het session, talbe, excel  cua form nay
                if (File.Exists(Session["filePath"].ToString()))
                    File.Delete(Session["filePath"].ToString());
                Session["filePath"] = "";
                Session["importTable"] = "";
                litData.Text = "";
            }
            catch (Exception ee)
            {
                ShowError(ee);
            }


        }
        protected void btnTemplate_Click(object sender, EventArgs e)
        {
            Response.Redirect("Shop_Upload.xls");
        }

        public void ShowError(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification error\"><div class=\"text\"><p><strong>" + er + " </strong> </p></div></div>", false);
        }
        public void ShowError(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification error\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowInfo(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification info\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowInfo(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification info\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowWarning(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification warning\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowWarning(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification warning\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowSuccess(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification success\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowSuccess(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification success\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowTip(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification tip\"><div class=\"text\"><p><strong> " + er + "</strong> </p></div></div>", false);
        }
        public void ShowTip(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification tip\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }

        protected void btnShowUploadResult_Click(object sender, EventArgs e)
        {
            litData.Text = readExcel(false);
        }
    }
}